﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FTools.Dominio;
using System.Data.SqlClient;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using FTools.Generico;


namespace FTools.Persistencia
{
    class PerPersonaJuridica
    {
        static string msg = "";

        //OBTENER PERSONA JURIDICA
        public PersonaJuridica obtenerPersonaJuridica(SqlConnection xConexion, SqlTransaction xTransaccion, long xIdPersona)
        {
            PersonaJuridica objPersonaJuridica = new PersonaJuridica();
            DataTable dataTable = new DataTable();
            try
            {
                SqlCommand command = new SqlCommand("select * from PERSONA_JURIDICA where ID_PERSONA=@IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xIdPersona);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count >= 0)
                {
                    DataRow fila = dataTable.Rows[0];

                    objPersonaJuridica.IdPersona = (long)fila.ItemArray[0];
                    objPersonaJuridica.RazonSocial = (String)fila.ItemArray[1]; objPersonaJuridica.RazonSocial = objPersonaJuridica.RazonSocial.Trim();
                    objPersonaJuridica.Rut = (String)fila.ItemArray[2]; objPersonaJuridica.Rut = objPersonaJuridica.Rut.Trim();
                    objPersonaJuridica.Bps = (String)fila.ItemArray[3]; objPersonaJuridica.Bps = objPersonaJuridica.Bps.Trim();
                    objPersonaJuridica.Bse = (String)fila.ItemArray[4]; objPersonaJuridica.Bse = objPersonaJuridica.Bse.Trim();
                }
                return objPersonaJuridica;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //EXISTE PERSONA JURIDICA
        public Boolean existePersonaJuridica(SqlConnection xConexion, SqlTransaction xTransaccion, long xIdPersona)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlCommand command = new SqlCommand("select count(*) as EXISTE from PERSONA_JURIDICA where ID_PERSONA=@IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xIdPersona);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count >= 0)
                {
                    DataRow fila = dataTable.Rows[0];
                    if ((int)fila.ItemArray[0] > 0)
                    {
                        return true;
                    }
                }
                return false;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //OBTENER COLECCION PERSONA JURIDICA
        public ArrayList obtenerColeccionPersonaJuridica(SqlConnection xConexion, SqlTransaction xTransaccion)
        {
            SqlDataAdapter dataAdapter;
            DataTable dataTable = new DataTable();
            ArrayList colPersonaJuridica = new ArrayList();
            try
            {
                SqlCommand command = new SqlCommand("select * from PERSONA_JURIDICA");

                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    PersonaJuridica objPersonaJuridica = new PersonaJuridica();
                    DataRow fila = dataTable.Rows[i];
                    objPersonaJuridica.IdPersona = (long)fila.ItemArray[0];
                    objPersonaJuridica.RazonSocial = (String)fila.ItemArray[1]; objPersonaJuridica.RazonSocial = objPersonaJuridica.RazonSocial.Trim();
                    objPersonaJuridica.Rut = (String)fila.ItemArray[2]; objPersonaJuridica.Rut = objPersonaJuridica.Rut.Trim();
                    objPersonaJuridica.Bps = (String)fila.ItemArray[3]; objPersonaJuridica.Bps = objPersonaJuridica.Bps.Trim();
                    objPersonaJuridica.Bse = (String)fila.ItemArray[4]; objPersonaJuridica.Bse = objPersonaJuridica.Bse.Trim();
                    colPersonaJuridica.Add(objPersonaJuridica);
                }
                return colPersonaJuridica;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //OBTENER BUSQUEDA PERSONA JURIDICA
        public ArrayList obtenerBusquedaPersonaJuridica(SqlConnection xConexion, SqlTransaction xTransaccion, String xRazonSocial, String xRut, String xBps, String xBse)
        {
            SqlDataAdapter dataAdapter;
            DataTable dataTable = new DataTable();
            ArrayList colPersonaJuridica = new ArrayList();

            try
            {
                SqlCommand command = new SqlCommand("select * from PERSONA_JURIDICA where " +
                                                    "(RAZON_SOCIAL like '%' + @RazonSocial + '%' or @RazonSocial='') and " +
                                                    "(RUT=@Rut or @Rut='') and " +
                                                    "(BPS=@Bps or @Bps='') and " +
                                                    "(BSE=@Bse or @Bse='')");

                command.Parameters.AddWithValue("@RazonSocial", xRazonSocial.Trim());
                command.Parameters.AddWithValue("@Rut", xRut.Trim());
                command.Parameters.AddWithValue("@Bps", xBps.Trim());
                command.Parameters.AddWithValue("@Bse", xBse.Trim());

                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    PersonaJuridica objPersonaJuridica = new PersonaJuridica();
                    DataRow fila = dataTable.Rows[i];
                    objPersonaJuridica.IdPersona = (long)fila.ItemArray[0];
                    objPersonaJuridica.RazonSocial = (String)fila.ItemArray[1]; objPersonaJuridica.RazonSocial = objPersonaJuridica.RazonSocial.Trim();
                    objPersonaJuridica.Rut = (String)fila.ItemArray[2]; objPersonaJuridica.Rut = objPersonaJuridica.Rut.Trim();
                    objPersonaJuridica.Bps = (String)fila.ItemArray[3]; objPersonaJuridica.Bps = objPersonaJuridica.Bps.Trim();
                    objPersonaJuridica.Bse = (String)fila.ItemArray[4]; objPersonaJuridica.Bse = objPersonaJuridica.Bse.Trim();
                    colPersonaJuridica.Add(objPersonaJuridica);
                }
                return colPersonaJuridica;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //ALTA PERSONA JURIDICA
        public int altaPersonaJuridica(SqlConnection xConexion, SqlTransaction xTransaccion, PersonaJuridica xObjPersonaJuridica)
        {
            try
            {
                SqlCommand command = new SqlCommand("insert into PERSONA_JURIDICA values (@IdPersona, @RazonSocial, @Rut, @Bps, @Bse)");

                command.Parameters.AddWithValue("@IdPersona", xObjPersonaJuridica.IdPersona);
                command.Parameters.AddWithValue("@RazonSocial", xObjPersonaJuridica.RazonSocial.Trim());
                command.Parameters.AddWithValue("@Rut", xObjPersonaJuridica.Rut.Trim());
                command.Parameters.AddWithValue("@Bps", xObjPersonaJuridica.Bps.Trim());
                command.Parameters.AddWithValue("@Bse", xObjPersonaJuridica.Bse.Trim());
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //BAJA PERSONA JURIDICA
        public int bajaPersonaJuridica(SqlConnection xConexion, SqlTransaction xTransaccion, PersonaJuridica xObjPersonaJuridica)
        {
            try
            {
                SqlCommand command = new SqlCommand("delete from PERSONA_JURIDICA where ID_PERSONA=@IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xObjPersonaJuridica.IdPersona);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //MODIFICA PERSONA JURIDICA
        public int modificaPersonaJuridica(SqlConnection xConexion, SqlTransaction xTransaccion, PersonaJuridica xObjPersonaJuridica)
        {
            try
            {
                SqlCommand command = new SqlCommand("update PERSONA_JURIDICA set RAZON_SOCIAL=@RazonSocial, RUT=@Rut, BPS=@Bps, BSE=@Bse " +
                                                    "where ID_PERSONA = @IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xObjPersonaJuridica.IdPersona);
                command.Parameters.AddWithValue("@RazonSocial", xObjPersonaJuridica.RazonSocial.Trim());
                command.Parameters.AddWithValue("@Rut", xObjPersonaJuridica.Rut.Trim());
                command.Parameters.AddWithValue("@Bps", xObjPersonaJuridica.Bps.Trim());
                command.Parameters.AddWithValue("@Bse", xObjPersonaJuridica.Bse.Trim());
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }
    }
}
